package cn.edu.zjgsu.ito.service;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import cn.edu.zjgsu.ito.model.College;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class AdminColManageService {

	/**
	 * 根据上传的EXCEL表格，获取所有学院信息并存入数据库
	 * 黄凯航
	 * @throws IOException 
	 * @throws BiffException 
	 */
	public String readCollegeTemplate(String fileName,String newFileName,String filePath) throws BiffException, IOException
	{
		Date date = new Date();
		DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int i=0;
		int j=1;
		int k=-1;
		int rows=0;
		int breakRows=1;
		int breakExcel=1;
		int breakK=0;
		int breakName=0;
		int breakCode=0;
		int baseOnName=0;
		int baseOnCode=0;
		int countSuccess=0;
		int countUpdate=0;
		int countFail=0;

		String error="";
		String breakCollegeName="";
		String breakCollegeCode="";
		String type="College Template（学院模板）";
		String uploader="Admin（教学秘书）";
		String uploadTime=format.format(date);

		Record uploadFileRecord = new Record().set("type", type)
				.set("fileName", fileName)
				.set("newFileName", newFileName)
				.set("filePath", filePath)
				.set("uploader", uploader)
				.set("uploadTime", uploadTime);
		Db.save("upload_file", uploadFileRecord);

		Workbook rwb = null;
		Cell[] cells=new Cell[2];
		InputStream stream = new FileInputStream(filePath+"\\"+newFileName);
		rwb=Workbook.getWorkbook(stream);
		Sheet sheet = rwb.getSheet(0);
		cells[0]=sheet.getCell(0,j);
		cells[1]=sheet.getCell(1,j);
		rows=sheet.getRows();

		String[] collegeNames=new String[rows-1];
		String[] collegeCodes=new String[rows-1];

		while(true)
		{
			breakRows++;

			if(cells[0].getContents().trim().equals("")||cells[0].getType()==CellType.EMPTY||
					cells[1].getContents().trim().equals("")||cells[1].getType()==CellType.EMPTY)
			{
				if(breakRows==rows)
					break;
				j++;
				cells[0]=sheet.getCell(0,j);
				cells[1]=sheet.getCell(1,j);
				continue;
			}
			k++;
			collegeNames[k]=cells[0].getContents().trim();
			collegeCodes[k]=cells[1].getContents().trim();
			breakK=k;
			if(breakRows==rows)
				break;
			j++;
			cells[0]=sheet.getCell(0,j);
			cells[1]=sheet.getCell(1,j);
		}

		for(j=0;j<=breakK;j++)
		{
			if(breakExcel==0)
				break;
			for(k=j+1;k<=breakK;k++)
			{
				if(collegeNames[j].equals(collegeNames[k]))
				{
					breakExcel=0;
					breakCollegeName=collegeNames[j];
					breakName=1;
					break;
				}
				if(collegeCodes[j].equals(collegeCodes[k]))
				{
					breakExcel=0;
					breakCollegeCode=collegeCodes[j];
					breakCode=1;
					break;
				}
			}
		}

		if(breakExcel==1)
		{
			breakRows=1;
			j=1;
			cells[0]=sheet.getCell(0,j);
			cells[1]=sheet.getCell(1,j);

			while(true)
			{
				baseOnName=0;
				baseOnCode=0;
				breakRows++;

				if(cells[0].getContents().trim().equals("")||cells[0].getType()==CellType.EMPTY||
						cells[1].getContents().trim().equals("")||cells[1].getType()==CellType.EMPTY)
				{
					countFail++;
					j++;
					if(breakRows==rows)
						break;
					else
					{
						cells[0]=sheet.getCell(0,j);
						cells[1]=sheet.getCell(1,j);
					}
					continue;
				}

				List<Record> collegeInfo=Db.find("select * from college");

				for(i=0;i<collegeInfo.size();i++)
				{
					Record singleCollegeInfo=collegeInfo.get(i);
					String collegeName=singleCollegeInfo.get("collegeName");
					if(collegeName.equals(cells[0].getContents().trim()))
					{
						List<Record> collegetemp=Db.find("select * from college where collegeCode=?",cells[1].getContents().trim());
						if(collegetemp.size()==0)
						{
							baseOnName=1;
							break;
						}
						else
						{
							countFail++;
							break;
						}
					}
				}

				if(baseOnName==0)
				{
					for(i=0;i<collegeInfo.size();i++)
					{
						Record singleCollegeInfo=collegeInfo.get(i);
						String collegeCode=singleCollegeInfo.get("collegeCode");
						if(collegeCode.equals(cells[1].getContents().trim()))
						{
							baseOnCode=1;
							break;
						}
					}
				}

				if(baseOnName==0&&baseOnCode==0)
				{
					Record collegeRecord=new Record()
					.set("collegeName", cells[0].getContents().trim())
					.set("collegeCode", cells[1].getContents().trim());
					Db.save("college", collegeRecord);
					countSuccess++;
				}

				else
				{
					if(baseOnName==1)
					{
						Db.update("update college set collegeCode=? where collegeName=?",
								cells[1].getContents().trim(),
								cells[0].getContents().trim());
						boolean WARMING=updateAllTablesByName(cells[0].getContents().trim(), cells[1].getContents().trim());
						if(WARMING==false)
							System.out.println("出事了！！！");
						countUpdate++;
					}
					else
					{
						if(baseOnCode==1)
						{
							Db.update("update college set collegeName=? where collegeCode=?",
									cells[0].getContents().trim(),
									cells[1].getContents().trim());
							boolean WARMING=updateAllTablesByName(cells[0].getContents().trim(), cells[1].getContents().trim());
							if(WARMING==false)
								System.out.println("出事了！！！");
							countUpdate++;
						}
					}
				}
				j++;
				if(breakRows==rows)
					break;
				else
				{
					cells[0]=sheet.getCell(0,j);
					cells[1]=sheet.getCell(1,j);
				}

			}
			error="成功导入"+countSuccess+"条记录；成功覆盖"+countUpdate+"条记录；导入失败"+countFail+"条记录";
			return error;
		}
		else
		{
			if(breakName==1)
				error="所上传的EXCEL表中存在数据重复----学院名称："+breakCollegeName;
			else
			{
				if(breakCode==1)
					error="所上传的EXCEL表中存在数据重复----学院代码："+breakCollegeCode;
			}
			return error;
		}
	}


	/**
	 * 添加单个学院方法
	 * 黄凯航
	 */
	public String addSingleCollege(College college)
	{
		String collegeName=college.getStr("collegeName").trim();
		String collegeCode=college.getStr("collegeCode").trim();
		String error="";
		if(collegeName.trim().equals("")||collegeName==null||
				collegeCode.trim().equals("")||collegeCode==null)
		{
			error="请填写所有必填信息";
			return error;
		}
		
		List<Record> colleges=Db.find("select * from college where collegeName=?",collegeName);		
		List<Record> collegetemp=Db.find("select * from college where collegeCode=?",collegeCode);
		if(colleges.size()==0&&collegetemp.size()==0)
		{
			college.save();
			error="新增成功";
		}
		if(colleges.size()==1&&collegetemp.size()==0)
		{
//			Db.update("update college set collegeCode=? where collegeName=?",collegeCode,collegeName);
//			boolean WARMING=updateAllTablesByName(collegeName, collegeCode);
//			if(WARMING==false)
//				System.out.println("出事了！！！");
//			error="新增成功";
			error="新增失败："+collegeName+"已存在";
		}
		if(colleges.size()==0&&collegetemp.size()==1)
		{
//			Db.update("update college set collegeName=? where collegeCode=?",collegeName,collegeCode);
//			boolean WARMING=updateAllTablesByName(collegeName, collegeCode);
//			if(WARMING==false)
//				System.out.println("出事了！！！");
//			error="新增成功";
			error="新增失败："+collegeCode+"已存在";
		}
		if(colleges.size()==1&&collegetemp.size()==1)
			error="新增失败："+collegeName+"和"+collegeCode+"皆已存在";
		return error;
	}
	
	
	/**
	 * 更新单个学院方法
	 * 黄凯航
	 */
	public String updateOrNot(College college)
	{
		int id=college.getInt("id");
		String collegeName=college.getStr("collegeName");
		String collegeCode=college.getStr("collegeCode");
		String error="";
		if(collegeName.trim().equals("")||collegeName==null||
				collegeCode.trim().equals("")||collegeCode==null||
				id<=0)
		{
			error="请填写所有必填信息";
			return error;
		}
		
		List<Record> colleges=Db.find("select * from college where collegeCode=?",collegeCode);
		if(colleges.size()==0)
		{
			college.update();
			boolean WARMING=updateAllTablesByName(collegeName, collegeCode);
			if(WARMING==false)
				System.out.println("出事了！！！");
			error="修改成功";
		}
		else
		{
			if(colleges.size()==1)
			{
				if((colleges.get(0).getInt("id")==id)&&(!collegeCode.equals(colleges.get(0).getStr("collegeCode"))))
				{
					college.update();
					boolean WARMING=updateAllTablesByName(collegeName, collegeCode);
					if(WARMING==false)
						System.out.println("出事了！！！");
					error="修改成功";
				}
				else
					error="没有数据更新，无效操作";
			}
		}
		return error;
	}
	
	
	/**
	 * 删除学院方法
	 * 黄凯航
	 */
	public void deleteOrNot(String collegeName)
	{
		int course=1;
		int major=1;
		int teacherinfo=1;
		
		List<Record> cNullOrNot=Db.find("select * from course where collegeName=?",collegeName);
		List<Record> mNullOrNot=Db.find("select * from major where collegeName=?",collegeName);
		List<Record> tiNullOrNot=Db.find("select * from teacher_info where collegeName=?",collegeName);
		
		if(cNullOrNot.size()==0)
			course=0;
		if(mNullOrNot.size()==0)
			major=0;
		if(tiNullOrNot.size()==0)
			teacherinfo=0;
		
		if(course==0&&major==0&&teacherinfo==0)
			Db.update("delete from college where collegeName=?",collegeName);
	}
	
	
	/**
	 * 更新所有表格一
	 * 黄凯航
	 */
	public boolean updateAllTablesByName(String collegeName,String collegeCode)
	{
		int caseOne=Db.update("update course set collegeCode=? where collegeName=?",collegeCode,collegeName);
		int caseTwo=Db.update("update major set collegeCode=? where collegeName=?",collegeCode,collegeName);
		int caseThree=Db.update("update teacher_info set collegeCode=? where collegeName=?",collegeCode,collegeName);
		
		if(caseOne>0&&caseTwo>0&&caseThree>0)
			return true;
		else
			return false;
	}
	
	
	/**
	 * 更新所有表格二
	 * 黄凯航
	 */
	public boolean updateAllTablesByCode(String collegeName,String collegeCode)
	{
		int caseOne=Db.update("update course set collegeName=? where collegeCode=?",collegeName,collegeCode);
		int caseTwo=Db.update("update major set collegeName=? where collegeCode=?",collegeName,collegeCode);
		int caseThree=Db.update("update teacher_info set collegeName=? where collegeCode=?",collegeName,collegeCode);
		
		if(caseOne>0&&caseTwo>0&&caseThree>0)
			return true;
		else
			return false;
	}
}
